package system.runner;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

@Component
public class DBTimezoneValidator implements ApplicationRunner {

    @Autowired
    private DataSource dataSource;

    @Override
    public void run(ApplicationArguments args) throws Exception {
        final long THRESHOLD_MINUTES = 5L;
        validate(dataSource, THRESHOLD_MINUTES);
    }

    protected void validate(DataSource dataSource, long thresholdMinutes) throws Exception {
        final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        // 获取应用服务器当前时间
        Date appDate = new Date();
        String appTimeStr = dateFormat.format(appDate);
        long appTime = appDate.getTime();

        // 获取数据库服务器当前时间
        Timestamp dbTimestamp = getDatabaseTime(dataSource);
        String dbTimeStr = dateFormat.format(dbTimestamp);
        long dbTime = dbTimestamp.getTime();

        // 计算时间差（秒级精度）
        long diffSeconds = Math.abs((appTime - dbTime) / 1000);

        if (diffSeconds > thresholdMinutes * 60) {
            double diffMinutes = diffSeconds / 60.0;
            double diffHours = diffMinutes / 60.0;
            throw new RuntimeException(
                    String.format(
                            "时间检查失败！\n应用程序时间: %s\n数据库时间: %s\n时间差超过检测阈值(%d分钟)\n差值: %.1f分钟 / %.1f小时\n可能原因:\n1. 服务器时区配置不一致\n2. 数据库连接时区未配置或配置错误\n3. 系统时钟不同步",
                            appTimeStr, dbTimeStr, thresholdMinutes, diffMinutes, diffHours
                    )
            );
        }
    }

    protected Timestamp getDatabaseTime(DataSource dataSource) throws SQLException {
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            String pn = conn.getMetaData().getDatabaseProductName().toLowerCase();
            String query;
            switch (pn) {
                case "oracle":
                    query = "SELECT CAST(CURRENT_TIMESTAMP AS TIMESTAMP) FROM DUAL";
                    break;
                case "db2":
                    query = "SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1";
                    break;
                default:
                    // MySQL/MariaDB、PostgreSQL、SQLite、SQL Server
                    query = "SELECT CURRENT_TIMESTAMP";
                    break;
            }
            try (ResultSet rs = stmt.executeQuery(query)) {
                if (rs.next()) {
                    return rs.getTimestamp(1);
                }
            }
        }
        throw new SQLException("Failed to get database time");
    }
}
